﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Check connection to database
/// </summary>
public class Connection
{
    private SqlConnection myConnection = null;
    private SqlCommand myCommand = null;
    private SqlDataReader dataReader = null;

    private string connectionText = ConfigurationManager.ConnectionStrings["connection"].ToString();

    private void closeDB()
    {
        if (myConnection != null)
        {
            myConnection.Close();
            myConnection = null;
        }
    }

    public bool isConnectionDatabase()
    {
        try
        {
            myConnection = new SqlConnection(connectionText);
            myConnection.Open();
            myConnection.Close();
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            closeDB();
        }        
    }

    //Login account
    public bool loginManager(string user, string pass, string table)
    {
        try
        {
            string commandText = "SELECT [user],[pass] FROM " + table + " WHERE [user]='" + user + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(commandText, myConnection);

            myConnection.Open();

            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                if (pass.Trim().Equals(dataReader["pass"].ToString().Trim()))
                {
                    myConnection.Close();
                    return true;
                }                
            }
            return false;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            closeDB();
        }        
    }

    public bool loginCandidate(string email, string pass)
    {
        try
        {
            string commandText = "SELECT * FROM Candidate WHERE [email]='" + email + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(commandText, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();
            if (dataReader.HasRows)
            {
                if (pass.Trim().Equals(dataReader["pass"].ToString().Trim()))
                {
                    LocalVariables.ARRAY_INFOR_CANDIDATE = new string[9];
                    LocalVariables.ARRAY_INFOR_CANDIDATE[0] = dataReader["name"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[1] = dataReader["birthday"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[2] = dataReader["address"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[3] = dataReader["city"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[4] = dataReader["country"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[5] = dataReader["description"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[6] = dataReader["phone"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[7] = dataReader["avatar"].ToString();
                    LocalVariables.ARRAY_INFOR_CANDIDATE[8] = dataReader["pass"].ToString();
                    return true;
                }
                else
                {
                    LocalVariables.ARRAY_INFOR_CANDIDATE = null;
                }
            }
        }
        catch (Exception) {}
        finally { closeDB(); }
        return false;
    }
}